/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db.dao;

import core.Group;
import core.Student;
import java.util.List;

/**
 *
 * @author Mikael
 */
public class GroupDAO extends AbstractDAO {

    public static Group read(long primaryKey) {
        final String sql =
                "SELECT DISTINCT Groups.id, Groups.name, Groups.description, Groups.creator, Students.fname, Students.lname FROM Groups INNER JOIN Students ON Students.id=Groups.creator AND Groups.id = ?";
        return jdbc.queryForObject(sql, groupMapper, primaryKey);
    }

    public static List<Group> readAll() {
        final String sql =
                "SELECT DISTINCT Groups.id, Groups.name, Groups.description, Groups.creator, Students.fname, Students.lname FROM Groups INNER JOIN StudentGroupPairs ON StudentGroupPairs.groupId=Groups.id INNER JOIN Students ON Students.id=StudentGroupPairs.studentId";
        return jdbc.query(sql, groupMapper);
    }

    public static List<Group> readN(int startRow, int nRows) {
        final String sql =
                "SELECT DISTINCT Groups.id, Groups.name, Groups.description, Groups.creator, Students.fname, Students.lname FROM Groups INNER JOIN StudentGroupPairs ON StudentGroupPairs.groupId=Groups.id INNER JOIN Students ON Students.id=StudentGroupPairs.studentId LIMIT ?, ?";
        return jdbc.query(sql, groupMapper, startRow, nRows);
    }

    public static int getNRows() {
        final String sql =
                "SELECT COUNT(*) FROM Groups";
        return jdbc.queryForInt(sql, DUMMY);
    }

    public static List<Group> search(Group group) {
        String name1 = "";
        String name2 = "";

        String[] creators = group.getCreator().split(" ");

        name1 = creators[0];

        if(creators.length > 1){
            name2 = creators[1];
        }
        System.out.println(name1);
        System.out.println(name2);

        final String sql = "SELECT DISTINCT Groups.id, Groups.name, Groups.description, Groups.creator, Students.fname, Students.lname FROM Groups INNER JOIN StudentGroupPairs ON Groups.id=StudentGroupPairs.groupId AND Groups.name LIKE ? INNER JOIN Students ON Students.id=Groups.creator AND Students.fname LIKE ? AND Students.lname LIKE ?";
        return jdbc.query(sql, groupMapper,
                "%" + group.getName() + "%",
                "%" + name1 + "%",
                "%" + name2 + "%");
    }

    public static int update(Group group) {
        final String sql =
                "UPDATE Groups SET Groups.name = ?, Groups.description = ? WHERE Groups.id = ?";
        int nRows = jdbc.update(sql,
                new Object[]{
                    group.getName(),
                    group.getDescription(),
                    group.getId()
                });
        return nRows;
    }

    public static List<Student> getStudents(long primaryKey){
        final String sql =
                "SELECT * FROM Students INNER JOIN StudentGroupPairs ON Students.id=StudentGroupPairs.studentId AND StudentGroupPairs.groupId = ? ORDER BY Students.lname";
        return jdbc.query(sql, studentMapper, primaryKey);
    }

    public static boolean isOwner(long primaryKey, long studentId){
        final String sql =
                "SELECT Groups.creator FROM Groups WHERE Groups.id = " + primaryKey;
        return studentId == jdbc.queryForLong(sql, DUMMY);
    }

    public static boolean isMember(long primaryKey, long studentId){
        final String sql =
                "SELECT COUNT(*) FROM StudentGroupPairs WHERE StudentGroupPairs.groupId = " +
                primaryKey + " AND StudentGroupPairs.studentId = " + studentId;
        return jdbc.queryForInt(sql, DUMMY) > 0;
    }

    //returns the id of the inserted row instead of the number of affected rows.
    public static int create(Group group) {
        final String sql1 =
                "INSERT INTO Groups (name, description, creator) VALUES (?, ?, ?)";
        jdbc.update(sql1,
            new Object[]{group.getName(),
            group.getDescription(),
            group.getCreatorId()});

        final String sql2 = "SELECT Groups.id FROM Groups ORDER BY Groups.id DESC LIMIT 1";
        int id = jdbc.queryForInt(sql2, DUMMY);

        final String sql3 =
                "INSERT INTO StudentGroupPairs (studentId, groupId) VALUES (?, ?)";
        jdbc.update(sql3,
                new Object[]{group.getCreatorId(), id});

        return id;
    }

    public static int delete(Group group) {
        jdbc.update("DELETE FROM StudentGroupPairs WHERE StudentGroupPairs.groupId = ?",
                new Object[]{group.getId()});
        return jdbc.update("DELETE FROM Groups WHERE id = ?",
                new Object[]{group.getId()});
    }
    
    public static int join(long groupId, long studentId) {
        final String sql =
                "INSERT INTO StudentGroupPairs (studentId, groupId) VALUES (?, ?)";
        int nRows = jdbc.update(sql,
                new Object[]{studentId, groupId});
        return nRows;
    }

    public static int leave(long groupId, long studentId) {
        final String sql =
                "DELETE FROM StudentGroupPairs WHERE studentId = ? AND groupId = ?";
        int nRows = jdbc.update(sql,
                new Object[]{studentId, groupId});
        return nRows;
    }
}